spring jdbcTemplate 批量插入返回自增id

您所在的位置：网站首页 › jdbctemplate 批量insert › spring jdbcTemplate 批量插入返回自增id

spring jdbcTemplate 批量插入返回自增id

2023-12-27 08:59| 来源: 网络整理| 查看: 265

项目中一个业务场景包含两个相互依赖的批量插入，第二次批量插入依赖第一次批量插入数据的自增id。我们的工程依赖的spring jdbcTemplate，于是我就翻看了一下jdbcTemplate的源码，发现批量插入接口，只是单存的返回影响的列表，并没有实际意义。

ublic int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)

去spring jira搜了一下，果然有记录，由于解决方案依赖数据库驱动，不同版本的数据支持不一样，所以该解决方案并没有写入官方的api中，下面我贴一下自己使用实例。

自定义继承JdbcTemplate的子类

import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.*; import org.springframework.jdbc.support.JdbcUtils; import org.springframework.jdbc.support.KeyHolder; import java.sql.*; import java.util.List; public class CustomerJdbcTemplate extends JdbcTemplate { public int[] batchUpdate(final String sql,final BatchPreparedStatementSetter pss, final KeyHolder generatedKeyHolder) throws DataAccessException { return (int[]) execute( new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { return conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); } }, new PreparedStatementCallback() { public Object doInPreparedStatement(PreparedStatement ps) throws SQLException { if (logger.isDebugEnabled()) logger.debug("Executing batch SQL update and returning " + "generated keys [" + sql + "]"); try { int batchSize = pss.getBatchSize(); int totalRowsAffected = 0; int[] rowsAffected = new int[batchSize]; List generatedKeys = generatedKeyHolder.getKeyList(); generatedKeys.clear(); ResultSet keys = null; for (int i = 0; i < batchSize; i++) { pss.setValues(ps, i); rowsAffected[i] = ps.executeUpdate(); totalRowsAffected += rowsAffected[i]; try { keys = ps.getGeneratedKeys(); if (keys != null) { RowMapper rowMapper = new ColumnMapRowMapper(); RowMapperResultSetExtractor rse = new RowMapperResultSetExtractor(rowMapper, 1); generatedKeys.addAll((List) rse.extractData(keys)); } } finally { JdbcUtils.closeResultSet(keys); } } if (logger.isDebugEnabled()) logger.debug("SQL batch update affected " + totalRowsAffected + " rows and returned " + generatedKeys.size() + " keys"); return rowsAffected; } finally { if (pss instanceof ParameterDisposer) ((ParameterDisposer) pss).cleanupParameters(); } } }); } }

使用实例

final List users = new ArrayList(); for (int i = 0; i < 10; i++) { User user = new User(); user.setName("name"+1L); user.setAge(i) users.add(user); } String sql = "insert into user(name,age) values(?,?)"; GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder(); customerJdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { User user = users.get(i); ps.setString(1, user.getName()); ps.setInt(2, user.getAge()); } @Override public int getBatchSize() { return users.size(); } }, generatedKeyHolder); List objectMap = generatedKeyHolder.getKeyList(); for(Map map : objectMap){ System.out.println(map.get("GENERATED_KEY")); }

【本文地址】

spring jdbcTemplate 批量插入返回自增id

spring jdbcTemplate 批量插入返回自增id

今日新闻

推荐新闻